<?php
namespace CNEPAdmin\Controller;
use CNEPAdmin\Controller\CommonController;
//use Think\Controller;
class ExcelController extends CommonController {
    
    protected $_orderReport = "/CNEPAdmin/";
    protected $_tableNo = array(
        'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'
    );
    protected $_logisArr = null;
    protected $_statusArr = null;
    protected $_isFreezeArr = null;
    protected $_sexArr = null;

    protected function setCode() {
        $this->_code = "Excel";
    }
    
    /**
     * 处理用户数据
     * @param type $arr
     */
    protected function _dealDataForUser(&$arr) {
        $arr ['addTime'] = date("Y/m/d H:i:s", $arr ['addTime']);
        $arr ['lastLogin'] = $arr ['lastLogin'] > 0 ? date("Y/m/d H:i:s", $arr ['lastLogin']) : "";
        $arr ['isFreeze'] = $this->_isFreezeArr [intval($arr ['isFreeze'])];
        $arr ['sex'] = $this->_sexArr [intval($arr ['sex'])];
    }
    
    /**
     * 导出用户数据
     */
    public function userReport() {        
    	//数据
        $fieldsArr = array(
            'addTime' => '注册时间',
            'nickName' => '昵称',
            'phone' => '手机号',
            'email' => '邮箱',
            'sex' => '性别',
            'carBrand' => '车辆品牌',
            'carType' => '车型',
            'carNum' => '车牌号',
            'carMenName' => '车主姓名',
            'idcard' => '身份证',
            'canComment' => '评论状态',
            'isFreeze' => '冻结状态',
            'errorMsg' => '提示信息',
            'loginNum' => '登录总次数',
            'lastLogin' => '最近登录',
            'lastIp' => '登录IP',
            'channelID'=>'渠道编号'
        );
        $orderCon = new UserController();
    	$datList = $orderCon->getAllUserList(array_keys($fieldsArr));
        //1:已冻结;0:未冻结
        $this->_isFreezeArr = array(
            '正常',
            '已冻结',
        );
        // 性别:0,女;1:男;
        $this->_sexArr = array(
            '女',
            '男',
        );
        $title = '【中经汇通用户数据】'. '导出时间:'.date('Y-m-d H:i:s');
        
//        exit();
        $this->_exportExcel($title, $fieldsArr, $datList, '_dealDataForUser');
            
    }
  
    /**
     * 导出订单数据
     */
    public function orderReport() {
        
        $orderType = intval(I("sOrderType"));
        $funcName = 'orderReport' . $orderType;
        $this->$funcName();
    }
    /**
     * 处理商城订单数据
     * @param type $arr
     */
    protected function _dealDataForOrder0(&$arr) {
        $arr ['orderSn'] = $arr ['orderSn'] > 0 ? $arr ['orderSn'] : "无";
        $arr ['addTime'] = date("Y/m/d H:i:s", $arr ['addTime']);
        $arr ['totalPrice'] = $arr ['totalPrice'] > 0 ? $arr ['totalPrice'] : 0;
        $arr ['payPrice'] = $arr ['payPrice'] > 0 ? $arr ['payPrice'] : 0;
//                $arr ['shopName'] = strlen($arr ['shopName']) > 0 ? $arr ['shopName'] : "无";
        $arr ['address'] = strlen($arr ['address']) > 0 ? $arr ['address'] : "无";
        $arr ['payModeName'] = strlen($arr ['payModeName']) > 0 ? $arr ['payModeName'] : "无";
        $arr ['payTime'] = $arr ['payTime'] > 0 ? date("Y/m/d H:i:s", $arr ['payTime']) : "未支付";
//                $arr ['dealTime'] = $arr ['dealTime'] > 0 ? date("Y/m/d H:i:s", $arr ['dealTime']) : "为处理";
//                $arr ['isComment'] = intval($arr ['isComment']) === 0 ? "未评论" : "已评论";
        $statusStr = $this->_statusArr [intval($arr ['status'])];
        $arr ['logisticsNo'] = strlen(trim($arr ['logisticsNo'])) > 0 ? $arr ['logisticsNo'] : "无";
        $arr ['liushui'] = strlen(trim($arr ['liushui'])) > 0 ? $arr ['liushui'] : "无";
        $logisticsStatusStr = $this->_logisArr [intval($arr ['logisticsStatus'])];
        $arr ['statusStr'] = $statusStr . '/' . $logisticsStatusStr;
        
        $goodsNames = '';
        foreach ($arr['goodsList'] as $kl => $v1) {
            //$goodsNames .= ','.$v1['goodsName'] . '（'. $v1['num'] .'件）';
            $goodsNames .= ','.$v1['goodsName'];
        }
        $arr['goodsNames'] = trim($goodsNames,',');
    }
      
    //导出商城订单
    public function orderReport0(){
        
    	//数据
        $fieldsArr = array(
            'orderSn' => '订单号',
            'userPhone' => '账号（手机号）',
            'logisticsNo' => '物流单',
            'goodsNames' => '商品名称',
            'liushui' => '支付流水号',
            'consignee' => '收货人',
            'phone' => '手机号',
            'province' => '省',
            'city' => '市',
            'district' => '区/县',
            'address' => '地址',
            'numOfPack' => '商品件数',
            'totalPrice' => '总价（元）',
            'payPrice' => '应付（元）',
            'statusStr' => '订单状态',
            'payModeName' => '支付方式',
            'addTime' => '订单时间',
            'payTime' => '付款时间'
        );
        //数据
        $orderCon = new OrderController();
    	$orderList = $orderCon->getAllOrderList();
        
        $this->_statusArr = array(
            '未付款',
            '已付款',
            '已完成',
            '已取消',
        );
        $this->_logisArr = array(
            '未配送',
            '已配送',
        );
        $title = '【中经汇通订单数据】'. '导出时间:'.date('Y-m-d H:i:s');
        
//        exit();
        $this->_exportExcel($title, $fieldsArr, $orderList, '_dealDataForOrder0');
    }
    
      /**
     * 处理基本订单数据
     * @param type $arr
     */
    protected function _dealDataForOrder1(&$arr) {
        $arr ['orderSn'] = $arr ['orderSn'] > 0 ? $arr ['orderSn'] : "无";
        $arr ['addTime'] = date("Y/m/d H:i:s", $arr ['addTime']);
        $arr ['totalPrice'] = $arr ['totalPrice'] > 0 ? $arr ['totalPrice'] : '0.00';
        $arr ['payPrice'] = $arr ['payPrice'] > 0 ? $arr ['payPrice'] : '0.00';
        $arr ['payModeName'] = strlen($arr ['payModeName']) > 0 ? $arr ['payModeName'] : "无";
        $arr ['payTime'] = $arr ['payTime'] > 0 ? date("Y/m/d H:i:s", $arr ['payTime']) : "未支付";
        $arr ['isComment'] = intval($arr ['isComment']) === 0 ? "未评论" : "已评论";
        $arr ['statusStr'] = $this->_statusArr [intval($arr ['status'])];
        
        $goodsNames = '';
        foreach ($arr['goodsList'] as $kl => $v1) {
            //$goodsNames .= ','.$v1['goodsName'] . '（'. $v1['num'] .'件）';
			$goodsNames .= ','.$v1['goodsName'];
        }
        $arr['goodsNames'] = trim($goodsNames,',');
    }
    
    //导出商城订单
    public function orderReport1(){
        
    	//数据
        $fieldsArr = array(
            'orderSn' => '订单号',
            'userPhone' => '账号（手机号）',
            'goodsNames' => '商品名称',
            'liushui' => '支付流水号',
            'totalPrice' => '总价（元）',
            'payPrice' => '应付（元）',
            'numOfPack' => '商品件数',
            'nums' => '商品子件数',
            'statusStr' => '订单状态',
            'isComment' => '是否评论',
            'payModeName' => '支付方式',
            'addTime' => '订单时间',
            'payTime' => '付款时间'
        );
        //数据
        $orderCon = new OrderController();
    	$orderList = $orderCon->getAllOrderList();
        
        $this->_statusArr = array(
            '未付款',
            '已付款',
            '已完成',
            '已取消',
        );
        $title = '【中经汇通订单数据】'. '导出时间:'.date('Y-m-d H:i:s');
        
//        exit();
        $this->_exportExcel($title, $fieldsArr, $orderList, '_dealDataForOrder1');
            	
    }
    
    /**
     * 动态导出Excel
     * @param type $title excel表格中的标题
     * @param type $fieldsArr 数据库字段名称 => excel表格中的字段名称 array(
            'addTime' => '注册时间',
            'nickName' => '昵称',
            'phone' => '手机号',
            'email' => '邮箱',
            'sex' => '性别',
            'carBrand' => '车辆品牌',
            'carType' => '车型',
            'carNum' => '车牌号',
            'carMenName' => '车主姓名',
            'idcard' => '身份证',
            'canComment' => '评论状态',
            'isFreeze' => '冻结状态',
            'errorMsg' => '提示信息',
            'loginNum' => '登录总次数',
            'lastLogin' => '最近登录',
            'lastIp' => '登录IP'
        );
     * @param type $datList 从数据库中取出来的数据列表
     * @param type $dealFuncName 处理数据库中所取数据的方法名称，如"_dealDataForUser"
     */
    protected function _exportExcel($title, $fieldsArr, $datList, $dealFuncName) {
        // 计算 excel 表的起始与结尾的字母
        $count = count($fieldsArr);
        if($count > 0) {
            $this->_tableNo = array_slice($this->_tableNo, 0, $count);
        }
//        dump(end($this->_tableNo));
        $endNo = end($this->_tableNo);
        
        error_reporting(E_ALL);
    	ini_set('display_errors', TRUE);
    	ini_set('display_startup_errors', TRUE);
    	ini_set('memory_limit','1024M');
    	define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
    	 
        $path="Public/exportExcel/";
        if (!file_exists('./' . $path)) {//创建图片目录
        	if (mkdir('./' . $path, 0777, true) == false) {
                    logErr("生成文件失败", './Public/download_file_error');
        		exit();
        	} else if (chmod('./' . $path, 0777) == false) {
                    logErr("生成文件失败", './Public/download_file_error');
        		exit();
        	}
        }
        
        import('Excel/PHPExcel');
    	 
    	$objPHPExcel=new \PHPExcel();
    	
    	//设置属性
    	$objPHPExcel->getProperties()->setCreator("Liang-hanzi")
    	->setLastModifiedBy("Liang-hanzi")
    	->setTitle("中经汇通数据导出")
    	->setSubject("中经汇通数据导出")
    	->setDescription("中经汇通数据导出".date("Y-m-d"))
    	->setKeywords("中经汇通数据导出")
    	->setCategory("表单");
    	
    	//set font size bold
    	$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12);		//默认字体大小
    	$objPHPExcel->getActiveSheet()->getStyle('A2:' . $endNo . '2')->getFont()->setSize(12);		//字体大小
    	$objPHPExcel->getActiveSheet()->getStyle('A2:' . $endNo . '2')->getFont()->setBold(true);    //加粗
    	
    	
    	$objPHPExcel->getActiveSheet()->getStyle('A2:' . $endNo . '2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    	$objPHPExcel->getActiveSheet()->getStyle('A2:' . $endNo . '2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
    	
    	$objPHPExcel->getActiveSheet()->getStyle( 'A2:' . $endNo . '2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);			//背景样式
    	$objPHPExcel->getActiveSheet()->getStyle( 'A2:' . $endNo . '2')->getFill()->getStartColor()->setARGB('FF808080');					//背景颜色
    	$objPHPExcel->getActiveSheet()->getStyle( 'A2:' . $endNo . '2')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);  //字体颜色
    	
    	//设置列宽
        foreach ($this->_tableNo as $v) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth(20);
        }
    	    	
    	//设置行高度
    	$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
    	
    	//设置水平居中
    	$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        foreach ($this->_tableNo as $v) {
            $objPHPExcel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
    	
    	//设置垂直居中
    	$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    	
    	//
    	$objPHPExcel->getActiveSheet()->mergeCells('A1:' . $endNo . '1');
    	
    	
    	// set table header content
    	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $title);
        
        $it = 0;
        foreach ($fieldsArr as $v) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($this->_tableNo[$it] . '2', "$v ");
            $it ++;
        }
    	//dump($count);exit;
    	
    	// 写数据内容    Miscellaneous glyphs, UTF-8
    	$objPHPExcel->getActiveSheet()->getStyle('A3'.':' . $endNo . '3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    	$objPHPExcel->getActiveSheet()->getStyle('A3'.':' . $endNo . '3')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
    	$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(16);
        
    	
    	//dump($datList);exit;
    	for($i=0;$i<count($datList);$i++){
            $arr = $datList[$i];
            $this->$dealFuncName($arr);
            //dump($arr);exit;
            $itt = 0;
            foreach ($fieldsArr as $key => $value) {
            	//$objPHPExcel->getActiveSheet(0)->setCellValue($this->_tableNo[$itt] .($i+3), $arr[$key],\PHPExcel_Cell_DataType::TYPE_STRING);
            	if($key == "totalPrice" || $key == "payPrice"){
                    $objPHPExcel->getActiveSheet(0)->setCellValue($this->_tableNo[$itt] .($i+3), $arr[$key]);
                } else {
                    $objPHPExcel->getActiveSheet(0)->setCellValueExplicit($this->_tableNo[$itt] .($i+3), $arr[$key],\PHPExcel_Cell_DataType::TYPE_STRING);
                }
                $itt ++;
            }

            $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':'. $endNo .($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':'. $endNo .($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
    	}
    	// Rename worksheet
    	$objPHPExcel->getActiveSheet()->setTitle('数据导出');
    	
    	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
    	$objPHPExcel->setActiveSheetIndex(0);
    	
    	// Save Excel 95 file
    	$filename="report-".date('Ymd-His').".xls";
    	
    	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    	$objWriter->save($path.$filename);
    	
//        logErr('保存的文件:' . $path.$filename . "\n",'./Public/download_file_error');
//        downloadFile($_SERVER['DOCUMENT_ROOT'].'/'.$path, $filename);
        
    	redirect('/'.$path.$filename,0);
    	exit;
    }
	
	public function readExcel(){
		error_reporting(E_ALL);
		//date_default_timezone_set('Asia/ShangHai');
 
		/** PHPExcel_IOFactory */
		import('Excel/PHPExcel');
    	 
    	//$objPHPExcel=new \PHPExcel();
 
 
		// Check prerequisites
		if (!file_exists("youzhanfenlei.xls")) {
			exit("not found youzhanfenlei.xls.\n");
		}
 
		$reader = \PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿)
		$PHPExcel = $reader->load("youzhanfenlei.xls"); // 载入excel文件
		$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
 
		$dataset = array();
		/** 循环读取每个单元格的数据 */
		for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始
			for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
				//$dataset[] = $sheet->getCell($column.$row)->getValue();
				//echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />";
				$dataset[$row][] = $sheet->getCell($column.$row)->getValue();
				//echo $sheet->getCell($column.$row)->getValue()
			}
		}
		foreach($dataset AS $key=>$value){
			$shop = D('Shop')->where(array('shopName'=>$value[1]))->select();
			if($shop){
				foreach($shop AS $k=>$v){
					if(strstr($value[3],"中石油")){
						$data['headImg'] = 'zsy_shop.jpg';
						D('Shop')->where(array('shopID'=>$v['shopID']))->save($data);
					} elseif(strstr($value[3],"中海油")){
						$data['headImg'] = 'zhy_shop.jpg';
						D('Shop')->where(array('shopID'=>$v['shopID']))->save($data);
					} elseif(strstr($value[3],"中石化")){
						$data['headImg'] = 'zsh_sm_shop.jpg';
						D('Shop')->where(array('shopID'=>$v['shopID']))->save($data);
					} elseif(strstr($value[3],"中油bp")){
						$data['headImg'] = 'zybp_shop.jpg';
						D('Shop')->where(array('shopID'=>$v['shopID']))->save($data);
					} 
				}
				
			}
		}
		//var_dump($dataset);
	}
    

}